using System;
using System.Linq;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Reflection;

namespace Acme.Northwind.Install
{
	/// <summary>
	/// Provides installation functionality for databases
	/// </summary>
	public class UpgradeInstaller
	{
		#region member variables

		private static readonly Version _def_Version = new Version(-1, -1, -1, -1);

		private const string DEFAULT_NAMESPACE = "Acme.Northwind.Install";
		private const string MODELKEY = "1aeb9949-ba47-477b-bfdf-c06fe5872e85";
		private bool _newInstall = false;
		private Version _previousVersion = null;
		private Version _upgradeToVersion = new Version(0, 0, 0, 0, 3);
		private string _connectionString = string.Empty;
		private System.Data.SqlClient.SqlConnection _connection;
		private System.Data.SqlClient.SqlTransaction _transaction;
		private List<EmbeddedResourceName> _resourceNames = new List<EmbeddedResourceName>();

		#endregion

		/// <summary>
		/// Upgrades a database to the newest schema
		/// </summary>
		/// <param name="connectionString">The connection string to the database</param>
		/// <param name="newInstall">Determines if this is a new (create) operation or an upgrade</param>
		public static void UpgradeDatabase(string connectionString, bool newInstall)
		{
			try
			{
				var upgradeInstaller = new UpgradeInstaller(connectionString, newInstall);
				upgradeInstaller.Initialize();
				upgradeInstaller.RunUpgrade();
			}
			catch { throw; }
		}

		/// <summary>
		/// 
		/// </summary>
		public static void AzureCopyDatabase(InstallSettings settings)
		{
			var o = new AzureCopy();
			o.Run(settings);
		}

		#region construct / initialize

		private UpgradeInstaller(string connectionString, bool newInstall)
		{
			_previousVersion = new Version(_def_Version);
			_connectionString = connectionString;
			_newInstall = newInstall;
		}

		private void Initialize()
		{
			var settings = new nHydrateSetting();
			settings.Load(_connectionString);

			//Verify that this is the proper database for this model, if there is a previous key
			if (settings.ModelKey != Guid.Empty)
			{
				if (settings.ModelKey != new Guid(MODELKEY))
				{
					if (System.Windows.Forms.MessageBox.Show("The database being updated was created against a different model. This may cause database versioning issues if you continue.\n\nDo you wish to continue?", "Continue?", System.Windows.Forms.MessageBoxButtons.YesNo, System.Windows.Forms.MessageBoxIcon.Warning) != System.Windows.Forms.DialogResult.Yes)
					{
						return;
					}
				}
			}

			if (settings.dbVersion != string.Empty)
			{
				string[] versionNumbers = settings.dbVersion.Split('.');
				_previousVersion = new Version();
				_previousVersion.Major = int.Parse(versionNumbers[0]);
				_previousVersion.Minor = int.Parse(versionNumbers[1]);
				_previousVersion.Revision = int.Parse(versionNumbers[2]);
				_previousVersion.Build = int.Parse(versionNumbers[3]);
				if (versionNumbers.Length >= 5)
					_previousVersion.Generated = int.Parse(versionNumbers[4]);
			}
			var assem = Assembly.GetExecutingAssembly();
			var resourceNames = assem.GetManifestResourceNames();
			foreach (string resourceName in resourceNames)
			{
				var ern = new EmbeddedResourceName(resourceName);
				_resourceNames.Add(ern);
			}
		}

		#endregion

		private void RunUpgrade()
		{
			_connection = new System.Data.SqlClient.SqlConnection(_connectionString);
			_connection.Open();
			_transaction = _connection.BeginTransaction();
			try
			{
				var settings = new nHydrateSetting();
				settings.Load(_connectionString);

				var currentDbVersion = _def_Version;
				if (!string.IsNullOrEmpty(settings.dbVersion))
					currentDbVersion = new Version(settings.dbVersion.Split('.'));

				if (currentDbVersion > _upgradeToVersion)
				{
					System.Windows.Forms.MessageBox.Show("The current database version (" + currentDbVersion.ToString() + ") is greater than the current library (" + _upgradeToVersion.ToString() + "). The upgrade will not proceed.", "Error", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
					return;
				}

				this.UpgradeSchemaAndStaticData();

				var _failedScripts = new List<string>();
				this.ReinstallViews(_failedScripts);
				this.ReinstallStoredProcedures(_failedScripts);
				this.ReinstallFunctions(_failedScripts);
				this.RunErrors(_failedScripts);
				
				_transaction.Commit();

				settings.dbVersion = _upgradeToVersion.ToString(".");
				settings.LastUpdate = DateTime.Now;
				settings.ModelKey = new Guid(MODELKEY);
				settings.History.Add(new HistoryItem() { Version = _upgradeToVersion.ToString("."), PublishDate = DateTime.Now });
				settings.Save(_connectionString);

				SqlServers.DeleteExtendedProperty(_connectionString, "dbVersion");
				SqlServers.DeleteExtendedProperty(_connectionString, "LastUpdate");
				SqlServers.DeleteExtendedProperty(_connectionString, "ModelKey");
				SqlServers.DeleteExtendedProperty(_connectionString, "History");
			}
			catch (Exception ex)
			{
				System.Windows.Forms.MessageBox.Show(ex.Message, "Error!", System.Windows.Forms.MessageBoxButtons.OK, System.Windows.Forms.MessageBoxIcon.Error);
				_transaction.Rollback();
				throw;
			}
			finally
			{
				_connection.Close();
			}

		}

		private void UpgradeSchemaAndStaticData()
		{
			const string CREATE_SCHEMA_FILE = ".Create_Scripts.Generated.CreateSchema.sql";
			const string TRIGGER_FILE = ".Create_Scripts.Generated.CreateSchemaAuditTriggers.sql";
			const string STATIC_DATA_FILE = ".Create_Scripts.Generated.CreateData.sql";
			const string UPGRADE_FOLDER = ".Upgrade_Scripts";
			const string FIRST_UPGRADE = UPGRADE_FOLDER + ".FirstUpgrade.sql";
			const string INITIAL_CREATE = UPGRADE_FOLDER + ".InitialCreate.sql";
			const string UPGRADE_GENERATED_FOLDER = ".Upgrade_Scripts.Generated.";
			const string CREATE_FOLDER = ".Create_Scripts";
			try
			{
				var upgradeSchemaScripts = this.GetResourceNameUnderLocation(UPGRADE_GENERATED_FOLDER);
				var sortByVersionScripts = new SortedDictionary<string, EmbeddedResourceName>(upgradeSchemaScripts, new UpgradeFileNameComparer());

				//Run the generated upgrades
				if (!_previousVersion.Equals(_def_Version))
				{
					foreach (EmbeddedResourceName ern in sortByVersionScripts.Values)
					{
						if (new Version(ern.FileName).CompareTo(_previousVersion) > 0)
						{
							SqlServers.RunEmbeddedFile(_connection, _transaction, ern.FullName);
						}

					}
				}
				else if (!_newInstall)
				{
					//This is a first upgrade from a non-versioned database
					//Find the FirstUpgrade Script and run it.
					upgradeSchemaScripts = this.GetResourceNameUnderLocation(UPGRADE_FOLDER);
					{
						foreach (EmbeddedResourceName ern in upgradeSchemaScripts.Values)
						{
							if (ern.FullName.EndsWith(FIRST_UPGRADE, true, null))
							{
								SqlServers.RunEmbeddedFile(_connection, _transaction, ern.FullName);
							}
						}
					}
				}
				else
				{
					//This is a new installation (database creation)
				}

				//Run the create schema
				var scripts = this.GetResourceNameUnderLocation(CREATE_SCHEMA_FILE);
				foreach (EmbeddedResourceName ern in scripts.Values)
				{
					SqlServers.RunEmbeddedFile(_connection, _transaction, ern.FullName);
				}

				//Run the static data
				scripts = this.GetResourceNameUnderLocation(STATIC_DATA_FILE);
				foreach (var ern in scripts.Values)
				{
					SqlServers.RunEmbeddedFile(_connection, _transaction, ern.FullName);
				}

				//Run the triggers
				scripts = this.GetResourceNameUnderLocation(TRIGGER_FILE);
				foreach (var ern in scripts.Values)
				{
					SqlServers.RunEmbeddedFile(_connection, _transaction, ern.FullName);
				}

				//Run all other scripts
				scripts = this.GetResourceNameUnderLocation(CREATE_FOLDER);
				foreach (var ern in scripts.Values)
				{
					//Make sure it is not the two defined scripts
					if ((ern.AsmLocation != CREATE_SCHEMA_FILE) &&
						(ern.AsmLocation != STATIC_DATA_FILE) &&
						(ern.AsmLocation != TRIGGER_FILE))
						SqlServers.RunEmbeddedFile(_connection, _transaction, ern.FullName);
				}

				//This is a new installation so run the initialize script
				if (_newInstall)
				{
					upgradeSchemaScripts = this.GetResourceNameUnderLocation(INITIAL_CREATE);
					{
						foreach (EmbeddedResourceName ern in upgradeSchemaScripts.Values)
						{
							if (ern.FullName.EndsWith(INITIAL_CREATE, true, null))
							{
								SqlServers.RunEmbeddedFile(_connection, _transaction, ern.FullName);
							}
						}
					}
				}

				//Run the user-defined upgrades
				var excludePathList = new List<string>();
				excludePathList.Add(UPGRADE_GENERATED_FOLDER);
				excludePathList.Add(INITIAL_CREATE); 
				upgradeSchemaScripts = this.GetResourceNameUnderLocation(UPGRADE_FOLDER, excludePathList);
				sortByVersionScripts = new SortedDictionary<string, EmbeddedResourceName>(upgradeSchemaScripts, new UpgradeFileNameComparer());
				if (_previousVersion != null)
				{
					foreach (var ern in sortByVersionScripts.Values)
					{
						if (!ern.FullName.EndsWith(FIRST_UPGRADE, true, null)) //Ignore the first upgrade script
						{
							if (new Version(ern.FileName).CompareTo(_previousVersion) > 0)
							{
								SqlServers.RunEmbeddedFile(_connection, _transaction, ern.FullName);
							}
						}
					}
				}

			}
			catch { throw; }
		}

		private void RunErrors(List<string> _failedScripts)
		{
			if (_failedScripts == null) return;
			if (_failedScripts.Count == 0) return;

			var runFailed = true;
			do
			{
				var _scripts2 = new List<string>();
				foreach (var sql in _failedScripts)
				{
					SqlServers.ExecuteSQL(_connection, _transaction, sql, _scripts2);
				}
				runFailed = (_scripts2.Count < _failedScripts.Count && _scripts2.Count > 0);
				_failedScripts = _scripts2;
			} while (runFailed);

			if (_failedScripts.Count > 0)
			{
				//This will throw a proper error
				SqlServers.ExecuteSQL(_connection, _transaction, _failedScripts.First());
			}
		}

		private void ReinstallStoredProcedures(List<string> _failedScripts)
		{
			try
			{
				var storedProcedures = this.GetResourceNameUnderLocation(".Stored_Procedures");
				foreach (EmbeddedResourceName ern in storedProcedures.Values)
				{
					try
					{
						SqlServers.RunEmbeddedFile(_connection, _transaction, ern.FullName, _failedScripts);
					}
					catch
					{
						System.Windows.Forms.MessageBox.Show("Sp Fail: " + ern.FullName);
						throw;
					}
				}
			}
			catch { throw; }
		}

		private void ReinstallFunctions(List<string> _failedScripts)
		{
			try
			{
				var storedProcedures = this.GetResourceNameUnderLocation(".Functions");
				foreach (EmbeddedResourceName ern in storedProcedures.Values)
				{
					try
					{
						SqlServers.RunEmbeddedFile(_connection, _transaction, ern.FullName, _failedScripts);
					}
					catch
					{
						System.Windows.Forms.MessageBox.Show("Function Fail: " + ern.FullName);
						throw;
					}
				}
			}
			catch { throw; }
		}

		private void ReinstallViews(List<string> _failedScripts)
		{
			try
			{
				var storedProcedures = this.GetResourceNameUnderLocation(".Views");
				foreach (EmbeddedResourceName ern in storedProcedures.Values)
				{
					try
					{
						SqlServers.RunEmbeddedFile(_connection, _transaction, ern.FullName, _failedScripts);
					}
					catch
					{
						System.Windows.Forms.MessageBox.Show("Sp Fail: " + ern.FullName);
						throw;
					}
				}
			}
			catch { throw; }
		}

		private SortedDictionary<string, EmbeddedResourceName> GetResourceNameUnderLocation(string location)
		{
			var excludePathList = new List<string>();
			return GetResourceNameUnderLocation(location, excludePathList);
		}

		private SortedDictionary<string, EmbeddedResourceName> GetResourceNameUnderLocation(string location, List<string> excludePathList)
		{
			try
			{
				var retVal = new SortedDictionary<string, EmbeddedResourceName>();
				foreach (EmbeddedResourceName ern in _resourceNames)
				{
					if (ern.AsmLocation.StartsWith(location))
					{
						bool exclude = false;
						foreach (string path in excludePathList)
						{
							if (ern.AsmLocation.StartsWith(path))
								exclude = true;
						}

						if (!exclude) retVal.Add(ern.FullName, ern);
					}
				}
				return retVal;
			}
			catch { throw; }

		}

		private SortedDictionary<string, EmbeddedResourceName> GetResourceFileNameContains(string fileNamePart)
		{
			var retVal = new SortedDictionary<string, EmbeddedResourceName>();
			foreach (EmbeddedResourceName ern in _resourceNames)
			{
				if (ern.FileName.Contains(fileNamePart))
				{
					retVal.Add(ern.FileName, ern);
				}
			}
			return retVal;
		}

		#region UpgradeFileNameComparer class
		private class UpgradeFileNameComparer : IComparer<string>
		{

			#region IComparer<string> Members
			public int Compare(string x, string y)
			{
				return new Version(x).CompareTo(new Version(y));
			}
			#endregion
		}
		#endregion

		#region Version Class
		private class Version : IComparable<Version>
		{
			#region member variables
			int _major = 0;
			int _minor = 0;
			int _revision = 0;
			int _build = 0;
			int _generated = 0;
			#endregion

			public Version()
			{
			}

			public Version(int major, int minor, int revision, int build)
				: this()
			{
				_major = major;
				_minor = minor;
				_revision = revision;
				_build = build;
			}

			public Version(int major, int minor, int revision, int build, int generated)
				: this(major, minor, revision, build)
			{
				_generated = generated;
			}

			public Version(Version version)
			{
				_build = version._build;
				_generated = version._generated;
				_major = version._major;
				_minor = version._minor;
				_revision = version._revision;
			}

			public Version(string[] versionSplit)
				: this()
			{
				if (versionSplit.Length > 0) int.TryParse(versionSplit[0], out _major);
				if (versionSplit.Length > 1) int.TryParse(versionSplit[1], out _minor);
				if (versionSplit.Length > 2) int.TryParse(versionSplit[2], out _revision);
				if (versionSplit.Length > 3) int.TryParse(versionSplit[3], out _build);
				if (versionSplit.Length > 4) int.TryParse(versionSplit[4], out _generated);
			}

			public Version(string fileName)
			{
				const string UPGRADE_FOLDER = ".Upgrade_Scripts";
				const string FIRST_UPGRADE = UPGRADE_FOLDER + ".FirstUpgrade.sql";

				try
				{
					if (fileName.EndsWith(FIRST_UPGRADE, true, null))
					{
						_major = -45;
						return;
					}

					var arr1 = fileName.Split('.');
					string[] versionSplit = null;

					if (arr1.Length == 1)
						versionSplit = arr1[0].Split('_');
					else
						versionSplit = arr1[arr1.Length - 2].Split('_');

					if (versionSplit.Length > 0) int.TryParse(versionSplit[0], out _major);
					if (versionSplit.Length > 1) int.TryParse(versionSplit[1], out _minor);
					if (versionSplit.Length > 2) int.TryParse(versionSplit[2], out _revision);
					if (versionSplit.Length > 3) int.TryParse(versionSplit[3], out _build);
					if (versionSplit.Length > 4) int.TryParse(versionSplit[4], out _generated);
				}
				catch { }

			}

			#region Properties

			public int Major
			{
				get { return _major; }
				set { _major = value; }
			}

			public int Minor
			{
				get { return _minor; }
				set { _minor = value; }
			}

			public int Revision
			{
				get { return _revision; }
				set { _revision = value; }
			}

			public int Build
			{
				get { return _build; }
				set { _build = value; }
			}

			public int Generated
			{
				get { return _generated; }
				set { _generated = value; }
			}

			#endregion

			#region IComparable<Version> Members
			public int CompareTo(Version other)
			{
				if (this.Major != other.Major)
					return this.Major.CompareTo(other.Major);
				else if (this.Minor != other.Minor)
					return this.Minor.CompareTo(other.Minor);
				else if (this.Revision != other.Revision)
					return this.Revision.CompareTo(other.Revision);
				else if (this.Build != other.Build)
					return this.Build.CompareTo(other.Build);
				else if (this.Generated != other.Generated)
					return this.Generated.CompareTo(other.Generated);
				else
					return 0;
			}
			#endregion

			public override bool Equals(object obj)
			{
				if (!(obj is Version)) return false;
				var v = (Version)obj;
				return (v.Build == this.Build) &&
					(v.Generated == this.Generated) &&
					(v.Major == this.Major) &&
					(v.Minor == this.Minor) &&
					(v.Revision == this.Revision);
			}

			public override int GetHashCode()
			{
				return base.GetHashCode();
			}

			public string ToString(string seperationChars)
			{
				string retval = this.Major + seperationChars + this.Minor + seperationChars + this.Revision + seperationChars + this.Build;
				if (this.Generated > 0) retval += seperationChars + this.Generated;
				return retval;
			}

			public override string ToString()
			{
				return this.ToString(".");
			}

			public static bool operator <(Version r1, Version r2)
			{
				if ((object)r1 == null && (object)r2 == null) return false;
				if ((object)r1 == null ^ (object)r2 == null) return false;
				if (r1.Major < r2.Major) return true;
				if (r1.Minor < r2.Minor) return true;
				if (r1.Revision < r2.Revision) return true;
				if (r1.Build < r2.Build) return true;
				if (r1.Generated < r2.Generated) return true;
				return false;
			}

			public static bool operator >(Version r1, Version r2)
			{
				if ((object)r1 == null && (object)r2 == null) return false;
				if ((object)r1 == null ^ (object)r2 == null) return false;
				if (r1.Major > r2.Major) return true;
				if (r1.Minor > r2.Minor) return true;
				if (r1.Revision > r2.Revision) return true;
				if (r1.Build > r2.Build) return true;
				if (r1.Generated > r2.Generated) return true;
				return false;
			}

			public static bool operator ==(Version r1, Version r2)
			{
				if ((object)r1 == null && (object)r2 == null) return true;
				if ((object)r1 == null ^ (object)r2 == null) return false;
				if (r1 < r2) return false;
				if (r1 > r2) return false;
				return true;
			}

			public static bool operator !=(Version r1, Version r2)
			{
				if ((object)r1 == null && (object)r2 == null) return false;
				if ((object)r1 == null ^ (object)r2 == null) return true;
				if (r1 < r2) return true;
				if (r1 > r2) return true;
				return false;
			}

		}
		#endregion

		#region EmbeddedResourceName class
		private class EmbeddedResourceName
		{
			#region members
			private string _fullName;
			private string _fileName;
			private string _fileExtension;
			private string _asmLocation;
			private string _asmNamespace;
			#endregion

			#region Constructors
			public EmbeddedResourceName(string fullName)
			{
				string[] splitName = fullName.Split('.');
				_fullName = fullName;
				_fileName = splitName[splitName.Length - 2];
				_fileExtension = splitName[splitName.Length - 1];
				_asmNamespace = DEFAULT_NAMESPACE;
				int namespaceCount = DEFAULT_NAMESPACE.Split('.').Length;
				_asmLocation = string.Empty;
				for (int ii = namespaceCount; ii < splitName.Length; ii++)
				{
					_asmLocation += "." + splitName[ii];
				}
				_asmLocation.Trim(new char[] { '.' });
			}
			#endregion

			#region properties
			public string FullName
			{
				get { return _fullName; }
			}

			public string FileName
			{
				get { return _fileName; }
			}

			public string FileExtension
			{
				get { return _fileExtension; }
			}

			public string AsmLocation
			{
				get { return _asmLocation; }
			}

			public string AsmNamespace
			{
				get { return _asmNamespace; }
			}
			#endregion
		}
		#endregion

	}
}